MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一,这可能导致一些sql语句失效。
比如下表game: id group_id name score 1 A 小刚 20 2 B 小明 19 3 B 小花 17 4 C 小红 18
执行sql: select name, group_id from game group by group_id(记为sql_make_group) 返回:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'game.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
1
解决方法
把group by字段group_id设成primary key 或者 unique NOT NULL。这个方法在实际操作中没什么意义。
使用函数any_value把报错的字段name包含起来。如,select any_value(name), group_id from game group by group_id。
执行sql: select any_value(name) as name, group_id, min(score) as score from game group by group_id order by min(score)
返回结果 name group_id score 小明 B 17 小红 C 18 小刚 A 20
B组的name是小明(因为小明的id更小),而期望结果应该是小花。
所以单纯使用group by无法实现这样的需求。可以使用临时表的方法:
select id, name,t.group_id, t.score from (select group_id, min(score) as score from game group by group_id order by min(score)) t inner join game on t.group_id=game.group_id and t.score=game.score